'''

'''
import pymysql
from pymysql.cursors import DictCursor
from decimal import Decimal
import random
import time
import datetime
def rate(res):
    if not res['up_price']:
        res['up_price'] = 0
    if not res['flat_price']:
        res['flat_price'] = 0
    if not res['down_price']:
        res['down_price'] = 0
    all = res['up_price'] + res["flat_price"] + res["down_price"]
    if not all:
        up_rate = down_rate = flat_rate = 0
    else:
        up_rate = round(res['up_price'] / all,6)
        flat_rate = round(res['flat_price'] / all,6)
        down_rate = round(res['down_price'] / all,6)
    return up_rate,flat_rate,down_rate
def test():
    qhjy_db = pymysql.connect(host='192.168.1.100', user='root', password='root', database='qhjy', charset="utf8",
                              cursorclass=DictCursor)
    with qhjy_db as cur:
        sql = '''SELECT d.up_people_count as up_people ,e.flat_people_count flat_people , f.down_people_count as down_people , a.price as up_price , b.price as flat_price , c.price as down_price FROM
                             (
                               SELECT SUM(qhjy.qh_betting.price) as price , COUNT(qhjy.qh_betting.id) as people_count FROM qhjy.qh_betting
                                 INNER JOIN qhjy.qh_nper ON qhjy.qh_nper.id = qhjy.qh_betting.nper_id
                               WHERE qhjy.qh_nper.is_current = 3 AND qhjy.qh_nper.coin_id = 3 AND qhjy.qh_betting.type = 1 and qh_betting.is_true = 1 and qh_betting.user_id!=6369
                             ) a,
                             (
                               SELECT SUM(qhjy.qh_betting.price) as price , COUNT(qhjy.qh_betting.id) as people_count FROM qhjy.qh_betting
                                 INNER JOIN qhjy.qh_nper ON qhjy.qh_nper.id = qhjy.qh_betting.nper_id
                               WHERE qhjy.qh_nper.is_current = 3 AND qhjy.qh_nper.coin_id = 3 AND qhjy.qh_betting.type = 2 and qh_betting.is_true = 1 and qh_betting.user_id!=6369
                             ) b,
                             (
                               SELECT SUM(qhjy.qh_betting.price)  as price , COUNT(qhjy.qh_betting.id) as people_count FROM qhjy.qh_betting
                                 INNER JOIN qhjy.qh_nper ON qhjy.qh_nper.id = qhjy.qh_betting.nper_id
                               WHERE qhjy.qh_nper.is_current = 3 AND qhjy.qh_nper.coin_id = 3 AND qhjy.qh_betting.type = 3 and qh_betting.is_true = 1 and qh_betting.user_id!=6369
                             ) c,
                             (
                               SELECT count(_ciunt) as up_people_count  FROM (SELECT count(*) as _ciunt FROM qhjy.qh_betting
                                 INNER JOIN qhjy.qh_nper ON qhjy.qh_nper.id = qhjy.qh_betting.nper_id
                               WHERE qhjy.qh_nper.is_current = 3 AND qhjy.qh_nper.coin_id = 3 AND qhjy.qh_betting.type = 1 and qh_betting.is_true = 1 and qh_betting.user_id!=6369
                               GROUP BY qhjy.qh_betting.user_id) a
                             )d,
                             (
                               SELECT count(_ciunt) as flat_people_count FROM (SELECT count(*) as _ciunt FROM qhjy.qh_betting
                                 INNER JOIN qhjy.qh_nper ON qhjy.qh_nper.id = qhjy.qh_betting.nper_id
                               WHERE qhjy.qh_nper.is_current = 3 AND qhjy.qh_nper.coin_id = 3 AND qhjy.qh_betting.type = 2 and qh_betting.is_true = 1 and qh_betting.user_id!=6369
                               GROUP BY qhjy.qh_betting.user_id) a
                             )e,
                             (
                               SELECT count(_ciunt) as down_people_count  FROM (SELECT count(*) as _ciunt FROM qhjy.qh_betting
                                 INNER JOIN qhjy.qh_nper ON qhjy.qh_nper.id = qhjy.qh_betting.nper_id
                               WHERE qhjy.qh_nper.is_current = 3 AND qhjy.qh_nper.coin_id = 3 AND qhjy.qh_betting.type = 3 and qh_betting.is_true = 1 and qh_betting.user_id!=6369
                               GROUP BY qhjy.qh_betting.user_id) a
                             )f ;'''
        cur.execute(sql)
        res = cur.fetchone()
def levelling ():
    qhjy_db = pymysql.connect(host='192.168.1.100', user='root', password='root', database='qhjy', charset="utf8",
                              cursorclass=DictCursor)
    with qhjy_db as cur:
        cur.execute('select id,up_rate,down_rate,flat_rate from qh_nper where coin_id=3 and is_current = 3')
        res=cur.fetchone()
        nper_id=res["id"]
        sql = '''SELECT d.up_people_count as up_people ,e.flat_people_count flat_people , f.down_people_count as down_people , a.price as up_price , b.price as flat_price , c.price as down_price FROM
                     (
                       SELECT SUM(qhjy.qh_betting.price) as price , COUNT(qhjy.qh_betting.id) as people_count FROM qhjy.qh_betting
                         INNER JOIN qhjy.qh_nper ON qhjy.qh_nper.id = qhjy.qh_betting.nper_id
                       WHERE qhjy.qh_nper.is_current = 3 AND qhjy.qh_nper.coin_id = 3 AND qhjy.qh_betting.type = 1 and qh_betting.is_true = 1 and qh_betting.user_id!=6369
                     ) a,
                     (
                       SELECT SUM(qhjy.qh_betting.price) as price , COUNT(qhjy.qh_betting.id) as people_count FROM qhjy.qh_betting
                         INNER JOIN qhjy.qh_nper ON qhjy.qh_nper.id = qhjy.qh_betting.nper_id
                       WHERE qhjy.qh_nper.is_current = 3 AND qhjy.qh_nper.coin_id = 3 AND qhjy.qh_betting.type = 2 and qh_betting.is_true = 1 and qh_betting.user_id!=6369
                     ) b,
                     (
                       SELECT SUM(qhjy.qh_betting.price)  as price , COUNT(qhjy.qh_betting.id) as people_count FROM qhjy.qh_betting
                         INNER JOIN qhjy.qh_nper ON qhjy.qh_nper.id = qhjy.qh_betting.nper_id
                       WHERE qhjy.qh_nper.is_current = 3 AND qhjy.qh_nper.coin_id = 3 AND qhjy.qh_betting.type = 3 and qh_betting.is_true = 1 and qh_betting.user_id!=6369
                     ) c,
                     (
                       SELECT count(_ciunt) as up_people_count  FROM (SELECT count(*) as _ciunt FROM qhjy.qh_betting
                         INNER JOIN qhjy.qh_nper ON qhjy.qh_nper.id = qhjy.qh_betting.nper_id
                       WHERE qhjy.qh_nper.is_current = 3 AND qhjy.qh_nper.coin_id = 3 AND qhjy.qh_betting.type = 1 and qh_betting.is_true = 1 and qh_betting.user_id!=6369
                       GROUP BY qhjy.qh_betting.user_id) a
                     )d,
                     (
                       SELECT count(_ciunt) as flat_people_count FROM (SELECT count(*) as _ciunt FROM qhjy.qh_betting
                         INNER JOIN qhjy.qh_nper ON qhjy.qh_nper.id = qhjy.qh_betting.nper_id
                       WHERE qhjy.qh_nper.is_current = 3 AND qhjy.qh_nper.coin_id = 3 AND qhjy.qh_betting.type = 2 and qh_betting.is_true = 1 and qh_betting.user_id!=6369
                       GROUP BY qhjy.qh_betting.user_id) a
                     )e,
                     (
                       SELECT count(_ciunt) as down_people_count  FROM (SELECT count(*) as _ciunt FROM qhjy.qh_betting
                         INNER JOIN qhjy.qh_nper ON qhjy.qh_nper.id = qhjy.qh_betting.nper_id
                       WHERE qhjy.qh_nper.is_current = 3 AND qhjy.qh_nper.coin_id = 3 AND qhjy.qh_betting.type = 3 and qh_betting.is_true = 1 and qh_betting.user_id!=6369
                       GROUP BY qhjy.qh_betting.user_id) a
                     )f ;'''
        cur.execute(sql)
        res = cur.fetchone()
        if not res['up_price']:
            res['up_price'] = 0
        if not res['flat_price']:
            res['flat_price'] = 0
        if not res['down_price']:
            res['down_price'] = 0
        f_up_people,f_flat_people,f_down_people,f_up_price,f_flat_price,f_down_price=res.values()
        #真实倍率
        sql = '''SELECT d.up_people_count as up_people ,e.flat_people_count flat_people , f.down_people_count as down_people , a.price as up_price , b.price as flat_price , c.price as down_price FROM
                  (
                    SELECT SUM(qhjy.qh_betting.price) as price , COUNT(qhjy.qh_betting.id) as people_count FROM qhjy.qh_betting
                      INNER JOIN qhjy.qh_nper ON qhjy.qh_nper.id = qhjy.qh_betting.nper_id
                    WHERE qhjy.qh_nper.is_current = 3 AND qhjy.qh_nper.coin_id = 3 AND qhjy.qh_betting.type = 1 and qh_betting.is_true = 1
                  ) a,
                  (
                    SELECT SUM(qhjy.qh_betting.price) as price , COUNT(qhjy.qh_betting.id) as people_count FROM qhjy.qh_betting
                      INNER JOIN qhjy.qh_nper ON qhjy.qh_nper.id = qhjy.qh_betting.nper_id
                    WHERE qhjy.qh_nper.is_current = 3 AND qhjy.qh_nper.coin_id = 3 AND qhjy.qh_betting.type = 2 and qh_betting.is_true = 1
                  ) b,
                  (

                    SELECT SUM(qhjy.qh_betting.price)  as price , COUNT(qhjy.qh_betting.id) as people_count FROM qhjy.qh_betting
                      INNER JOIN qhjy.qh_nper ON qhjy.qh_nper.id = qhjy.qh_betting.nper_id
                    WHERE qhjy.qh_nper.is_current = 3 AND qhjy.qh_nper.coin_id = 3 AND qhjy.qh_betting.type = 3 and qh_betting.is_true = 1
                  ) c,
                  (
                    SELECT count(_ciunt) as up_people_count  FROM (SELECT count(*) as _ciunt FROM qhjy.qh_betting
                      INNER JOIN qhjy.qh_nper ON qhjy.qh_nper.id = qhjy.qh_betting.nper_id
                    WHERE qhjy.qh_nper.is_current = 3 AND qhjy.qh_nper.coin_id = 3 AND qhjy.qh_betting.type = 1 and qh_betting.is_true = 1
                    GROUP BY qhjy.qh_betting.user_id) a
                  )d,
                  (
                    SELECT count(_ciunt) as flat_people_count FROM (SELECT count(*) as _ciunt FROM qhjy.qh_betting
                      INNER JOIN qhjy.qh_nper ON qhjy.qh_nper.id = qhjy.qh_betting.nper_id
                    WHERE qhjy.qh_nper.is_current = 3 AND qhjy.qh_nper.coin_id = 3 AND qhjy.qh_betting.type = 2 and qh_betting.is_true = 1
                    GROUP BY qhjy.qh_betting.user_id) a
                  )e,
                  (
                    SELECT count(_ciunt) as down_people_count  FROM (SELECT count(*) as _ciunt FROM qhjy.qh_betting
                      INNER JOIN qhjy.qh_nper ON qhjy.qh_nper.id = qhjy.qh_betting.nper_id
                    WHERE qhjy.qh_nper.is_current = 3 AND qhjy.qh_nper.coin_id = 3 AND qhjy.qh_betting.type = 3 and qh_betting.is_true = 1
                    GROUP BY qhjy.qh_betting.user_id) a
                  )f ;'''
        cur.execute(sql)
        res = cur.fetchone()
        if not res['up_price']:
            res['up_price'] = 0
        if not res['flat_price']:
            res['flat_price'] = 0
        if not res['down_price']:
            res['down_price'] = 0
        r_up_people,r_flat_people,r_down_people,r_up_price,r_flat_price,r_down_price=res.values()
        max_price=max(f_down_price,f_flat_price,f_up_price)
        if max_price==f_up_price:
            need_f_down_price=(f_up_price/r_up_price)*r_down_price
            need_f_flat_price = (f_up_price / r_up_price) * r_flat_price
            need_f_up_price=float(0)
        if max_price == f_flat_price:
            need_f_down_price = (f_flat_price / r_flat_price) * r_down_price
            need_f_up_price = (f_flat_price / r_flat_price) * r_up_price
            need_f_flat_price=float(0)
        if max_price == f_down_price:
            need_f_up_price = (f_down_price / r_down_price) * r_up_price
            need_f_flat_price = (f_down_price / r_down_price) * r_flat_price
            need_f_down_price=float(0)
        t=int(time.time())
        if not need_f_up_price == 0:
            cur.execute(f'insert into qhjy.qh_betting(user_id,tw_qh_coin,nper_id,`type`,price,add_time,is_true) values(6369,3,{nper_id},1,{need_f_up_price},{t},1);')
        if  not need_f_flat_price == 0:
            cur.execute(f'insert into qhjy.qh_betting(user_id,tw_qh_coin,nper_id,`type`,price,add_time,is_true) values(6369,3,{nper_id},2,{need_f_flat_price},{t},1);')
        if  not need_f_down_price == 0:
            cur.execute(f'insert into qhjy.qh_betting(user_id,tw_qh_coin,nper_id,`type`,price,add_time,is_true) values(6369,3,{nper_id},3,{need_f_down_price},{t},1);')
        print('OK')
def datas():
    qhjy_db = pymysql.connect(host='192.168.1.100', user='root', password='root', database='qhjy', charset="utf8",
                              cursorclass=DictCursor)
    with qhjy_db as cur:
        cur.execute('select id,up_rate,down_rate,flat_rate from qh_nper where coin_id=3 and is_current = 3')
        res = cur.fetchone()
        print(res)
        nper_id = res["id"]
        for i in range(10):
            c=random.randint(1,3)
            t = int(time.time())
            if c==1:
                need_f_up_price=random.uniform(0.1,0.55)
                cur.execute(
                    f'insert into qhjy.qh_betting(user_id,tw_qh_coin,nper_id,`type`,price,add_time,is_true) values(6369,3,{nper_id},1,{need_f_up_price},{t},1);')
            if c==2:
                need_f_flat_price = random.uniform(0.1, 0.55)
                cur.execute(
                    f'insert into qhjy.qh_betting(user_id,tw_qh_coin,nper_id,`type`,price,add_time,is_true) values(6369,3,{nper_id},2,{need_f_flat_price},{t},1);')

            if c==3:
                need_f_down_price = random.uniform(0.1, 0.55)
                cur.execute(
                    f'insert into qhjy.qh_betting(user_id,tw_qh_coin,nper_id,`type`,price,add_time,is_true) values(6369,3,{nper_id},3,{need_f_down_price},{t},1);')
            time.sleep(1)

if __name__ == '__main__':
    # levelling()
    while True:
        minute = datetime.datetime.now().minute
        second = datetime.datetime.now().second
        if minute % 5 == 4 and second in [50,60]:
            print('调平了')
            start=time.time()
            levelling()
            end=time.time()
            if end-start>8:
                print(False)
            time.sleep(30)
            break
        elif minute % 5 != 4:
            datas()
            t=random.randint(1,8)
            time.sleep(t)


